
-- 查看表空间
SELECT tablespace_name, 
       SUM(bytes)/1024/1024 AS total_mb,
       SUM(maxbytes)/1024/1024 AS total_max_mb,
       SUM(user_bytes)/1024/1024 AS used_mb,
       SUM(maxbytes)/1024/1024 - SUM(user_bytes)/1024/1024 AS free_mb
FROM   dba_data_files
GROUP BY tablespace_name;

--查看sql耗时
SELECT sql_id, 
        sql_text,
        elapsed_time/1000000 AS elapsed_time_seconds,
         LAST_ACTIVE_TIME,
       plan_hash_value, 
       executions, 
       buffer_gets, 
       disk_reads, 
       rows_processed 
FROM   v$sql
WHERE  executions > 0 AND LAST_ACTIVE_TIME > TO_DATE('2024-03-15 23:00:00', 'yyyy-MM-dd hh24:mi:ss') 
ORDER BY  elapsed_time_seconds DESC , LAST_ACTIVE_TIME DESC ;


-- 迁移表
 ALTER TABLE your_table MOVE TABLESPACE another_tablespace;

-- 查看大表
 SELECT
    table_name,
    TO_CHAR(num_rows, '999,999,999,999') AS num_rows
 FROM
    dba_tables
 WHERE
    num_rows IS NOT NULL
 ORDER BY
    num_rows DESC;
 

-- 查看表占空间
SELECT segment_name AS table_name,
       ROUND(bytes/1024/1024/1024, 2)  AS size_gb
FROM   dba_segments
WHERE  segment_type = 'TABLE'
ORDER BY size_gb DESC;


-- 查看表活动
SELECT * FROM   fa_card AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);


-- 查看最近活动sql
SELECT sql_text, 
       last_active_time, 
       executions, 
       buffer_gets, 
       disk_reads, 
       rows_processed
FROM   v$sqlarea 
ORDER BY last_active_time DESC;


-- 查看阻塞者信息
SELECT * FROM dba_blockers;

-- 查看等待者信息
SELECT * FROM dba_waiters;

-- 查看表空间下的大表
SELECT segment_name AS table_name,
       tablespace_name,
       ROUND(SUM(bytes)/1024/1024/1024, 2)  AS size_gb
FROM   dba_segments
WHERE  tablespace_name = 'NNC_DATA01'
GROUP BY segment_name, tablespace_name
ORDER BY SUM(bytes) DESC



-- 查看大表行数
SELECT
	table_name,
	TO_CHAR(num_rows, '999,999,999,999') AS num_rows
FROM
	dba_tables
WHERE
	num_rows IS NOT NULL
ORDER BY
	num_rows DESC;


-- 锁表与解锁
SELECT 
    l.session_id AS sid, 
    s.serial#, 
    l.locked_mode, 
    l.oracle_username, 
    l.os_user_name, 
    s.machine, 
    s.terminal, 
    a.sql_text
FROM 
    v$locked_object l
JOIN 
    v$session s ON l.session_id = s.sid
JOIN 
    dba_objects o ON l.object_id = o.object_id
LEFT JOIN 
    v$sqlarea a ON s.sql_hash_value = a.hash_value
WHERE 
    o.object_name = 'YOUR_TABLE_NAME';

--
ALTER SYSTEM KILL SESSION '768,5350';
  